//
//  SqliteManager.m
//  HandleFarmer
//
//  Created by Cheney Lau on 7/30/14.
//  Copyright (c) 2014 Handou. All rights reserved.
//

#import "SqliteManager.h"
#import "FMDB.h"
#import "AppDataManager.h"

// FMDB检查执行命令是否成功
#define FMDBQuickCheck(SomeBool) { if (!(SomeBool)) { DLogLine(@"Failure on line %d", __LINE__); abort(); } }

static NSString *dbPath = @"autohome.db"; //这个是放在Documents下的目录：Documents/sqlite/default.db
static NSString *SqliteVersion = @"1.0"; //当有表结构更新的时候，更新版本号
static BOOL ReplaceSqlFileEveryTime = NO; // YES,每次运行软件时候都用bundle下的sql文件替换documents下的文件

@interface SqliteManager ()

@property (nonatomic,strong) NSString *absoluteDBPath; // db绝对位置

@end

@implementation SqliteManager

+ (instancetype)sharedInstance
{
    static id instance;
    static dispatch_once_t token;
    dispatch_once(&token, ^{
        instance = [[[self class] alloc] initWithPath:nil];
    });
    return instance;
}



- (id)initWithPath:(NSString *)absolutePath
{
    if(!(self = [super init]))
    {
        return nil;
    }
    
    if(!absolutePath)
    {
        NSArray *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
        
        NSString *documentsDirectory = [paths objectAtIndex:0];
        
        absolutePath = [documentsDirectory stringByAppendingPathComponent:dbPath];
        _absoluteDBPath = absolutePath;
//      NSLog(@"%@",absolutePath);
    }
    //每次都替换documents下的sql文件
    
    if (ReplaceSqlFileEveryTime) {
        if ([self deleteDBFile]) {
            DLogLine(@"删除数据库成功");
        }else DLogLine(@"删除数据库失败");
        if ([self moveDBFileFromBundleToDocuments]) {
            DLogLine(@"执行每次替换documents下sql文件成功！");
        }else DLogLine(@"执行每次替换documents下sql文件失败！");
    }
    
    //如果数据库不存在，创建数据库并初始化数据表
    //方式一：代码创建数据库
    /*
    if (![self databaseFileIsExisted]) {
        DLogLine(@"数据库文件不存在,开始创建，并初始化数据表");
        BOOL dbFileIsExistedInBundle = [self databaseFileIsExistedInBundle];
        if (dbFileIsExistedInBundle) {
            BOOL isMoved = [self moveDBFileFromBundleToDocuments];
            if (isMoved) {
                DLogLine(@"复制sql文件成功");
            }else DLogLine(@"复制sql文件失败");
        } else [self createDefautTables];
        
    };
     */
    //方式二：直接拷贝
    if (![self databaseFileIsExisted] || [AppDataManager sharedInstance].isFirstLaunch) {
        DLogLine(@"数据库文件不存在,开始创建，并初始化数据表");
        BOOL dbFileIsExistedInBundle = [self databaseFileIsExistedInBundle];
        if (dbFileIsExistedInBundle) {
            BOOL isMoved = [self moveDBFileFromBundleToDocuments];
            if (isMoved) {
                DLogLine(@"复制sql文件成功");
            }else DLogLine(@"复制sql文件失败");
        } else
        {
            DLogLine(@"数据库文件不存在在bundle中，无法复制！");
        };
        
    };
    
    //初始化DB
    self.db = [FMDatabase databaseWithPath:self.absoluteDBPath];
    if ([_db open]) {
        DLogLine(@"DBPath:%@",_absoluteDBPath);
        DLogLine(@"数据库打开成功");
        [self.db executeUpdate:@"CREATE TABLE IF NOT EXISTS \"newsdata\" (\"itemId\" INTEGER NOT NULL,\"dataArray\" TEXT NOT NULL,\"lasttime\" integer NOT NULL,PRIMARY KEY(\"itemId\"))"];
    }else
    {
        DLogLine(@"数据库打开失败");
    }
    
    return self;
}

#pragma mark - 数据库文件操作

- (BOOL)deleteDBFile
{
    NSFileManager *fileManager = [NSFileManager defaultManager];
    return [fileManager removeItemAtPath:_absoluteDBPath error:nil];
}

- (BOOL)databaseFileIsExisted
{
    NSFileManager *fileManager = [NSFileManager defaultManager];
    return [fileManager fileExistsAtPath:_absoluteDBPath];
}

- (BOOL)databaseFileIsExistedInBundle
{
    NSFileManager *fileManager = [NSFileManager defaultManager];
    NSString *sampleDBLocation = [[NSBundle mainBundle]pathForResource:[[dbPath componentsSeparatedByString:@"."] objectAtIndexedSubscript:0] ofType:@"db"];
    return [fileManager fileExistsAtPath:sampleDBLocation];
}

- (BOOL)moveDBFileFromBundleToDocuments
{
    NSFileManager *fm = [NSFileManager defaultManager];
    NSString *sampleDBLocation = [[NSBundle mainBundle]pathForResource:[[dbPath componentsSeparatedByString:@"."] objectAtIndexedSubscript:0] ofType:@"db"];
    NSData *data = [fm contentsAtPath:sampleDBLocation];
    NSArray *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
    NSString *documentsDirectory = [paths objectAtIndex:0];
    NSString *aPath = [documentsDirectory stringByAppendingPathComponent:dbPath];
    return [fm createFileAtPath:aPath contents:data attributes:nil];
}

#pragma mark - 创建数据库和表结构

- (BOOL)createDBFile
{
    DLogLine(@"dbPath:%@",_absoluteDBPath);
    self.db = [FMDatabase databaseWithPath:self.absoluteDBPath];
    if (![self.db open]) {
        DLogLine(@"不能打开数据库，可能文件不存在,或者没有权限在这个目录创建文件");
        return NO;
    }else
    {
        DLogLine(@"打开数据库成功");
        return YES;
    }
}

- (BOOL)createDefautTables
{
    if ([self createDBFile]) {
        DLogLine(@"开始创建数据表");
        [self.db executeUpdate:@"CREATE TABLE \"Hb_member\" (\"memberid\" INTEGER PRIMARY KEY  NOT NULL  DEFAULT (null) ,\"membername\" VARCHAR(32) DEFAULT (null) ,\"memberphone\" VARCHAR(32) DEFAULT (null) ,\"memberdelflg\" INTEGER DEFAULT (0) ,\"timestamp\" DATETIME DEFAULT (null) )"];
        [self.db executeUpdate:@"CREATE TABLE \"Hb_member_car\" (\"memcarid\" INTEGER PRIMARY KEY  NOT NULL  DEFAULT (null) ,\"memberid\" INTEGER NOT NULL  DEFAULT (null) ,\"memcarplate\" VARCHAR(32) DEFAULT (null) ,\"membercardelflg\" INTEGER DEFAULT (0) ,\"timestamp\" DATETIME DEFAULT (null) )"];
        [self.db executeUpdate:@"CREATE TABLE \"Hb_member_package\" (\"mempkgrelid\" INTEGER PRIMARY KEY  NOT NULL  DEFAULT (null) ,\"memberid\" INTEGER DEFAULT (null) ,\"memPkgid\" INTEGER,\"mempkgcnt\" INTEGER,\"memPkgbuydt\" DATETIME,\"memPkgbuytm\" DATETIME,\"mempkgdelflg\" INTEGER,\"timestamp\" DATETIME DEFAULT (null) )"];
        [self.db executeUpdate:@"CREATE TABLE \"Hb_package\" (\"packageid\" INTEGER PRIMARY KEY  NOT NULL  DEFAULT (null) ,\"packagename\" VARCHAR DEFAULT (null) ,\"packagecount\" INTEGER DEFAULT (null) ,\"packageprice\" FLOAT DEFAULT (null) ,\"packagedelflg\" INTEGER DEFAULT (null) ,\"timestamp\" DATETIME DEFAULT (null) )"];
        [self.db executeUpdate:@"CREATE TABLE \"Hb_repair_item\" (\"repairitemid\" INTEGER PRIMARY KEY  AUTOINCREMENT  NOT NULL , \"repairitemname\" VARCHAR, \"repairitemcost\" FLOAT, \"repairitembscost\" FLOAT, \"repairitemsortid\" INTEGER, \"repairitemdfsetflg\" INTEGER, \"repairitemdelflg\" INTEGER, \"timestamp\" DATETIME)"];
        [self.db executeUpdate:@"CREATE TABLE \"Hb_setting\" (\"setid\" INTEGER PRIMARY KEY  NOT NULL  DEFAULT (null) ,\"sitemcost\" FLOAT DEFAULT (null) ,\"sitembasecost\" FLOAT DEFAULT (null) ,\"setfinanceflg\" INTEGER DEFAULT (0) ,\"setdelflg\" INTEGER DEFAULT (0) ,\"timestamp\" DATETIME DEFAULT (null) )"];
        [self.db executeUpdate:@"CREATE TABLE \"Hb_staff\" (\"staffid\" INTEGER PRIMARY KEY  NOT NULL  DEFAULT (null) ,\"staffname\" VARCHAR(32) DEFAULT (null) ,\"staffdelflg\" INTEGER DEFAULT (0) ,\"timestamp\" DATETIME DEFAULT (null) )"];
        [self.db executeUpdate:@"CREATE TABLE \"Hb_update\" (\"updateid\" INTEGER PRIMARY KEY  AUTOINCREMENT  NOT NULL , \"updatestamp\" DATETIME)"];
        [self.db executeUpdate:@"CREATE TABLE \"Hb_work_orders\" (\"workid\" INTEGER PRIMARY KEY  NOT NULL ,\"workmemcarid\" INTEGER,\"workenterdt\" DATETIME,\"workentertm\" DATETIME,\"workpkgusecnt\" INTEGER,\"workcarplate\" VARCHAR,\"workdelflg\" INTEGER,\"timestamp\" DATETIME DEFAULT (null) )"];
        [self.db executeUpdate:@"CREATE TABLE \"Hb_work_orders_item\" (\"wiid\" INTEGER PRIMARY KEY  AUTOINCREMENT  NOT NULL , \"wiworkid\" INTEGER, \"wiitemid\" INTEGER, \"wiitemname\" VARCHAR, \"widelflg\" INTEGER, \"wicost\" FLOAT, \"wibasecost\" FLOAT, \"timestamp\" DATETIME)"];
        DLogLine(@"创建数据表完成");
        return YES;
    }else {
        DLogLine(@"创建数据表失败");
        return NO;
    }
}

#pragma mark - 已经创建数据库，进行CRUD操作

/**
 普通查询：执行结果是Bool
 */
- (BOOL)executeUpdateWithSql:(NSString *)sql andParam:(NSArray *)param
{
    BOOL rc = [_db executeUpdate:sql withArgumentsInArray:param];
    
    if (!rc) {
        DLogLine(@"ERROR: %d - %@", self.db.lastErrorCode, self.db.lastErrorMessage);
    }

    return rc;
}




/**
 带结果集查询：返回多行数据
 */
- (FMResultSet *)executeQueryWithSql:(NSString *)sql
{
    FMResultSet *rs = [self.db executeQuery:sql];
    if (!rs) {
        DLogLine(@"ERROR: %d - %@", self.db.lastErrorCode, self.db.lastErrorMessage);
    }
    
    return rs;
}

///**
//     带结果集查询：返回多行数据
// */
//- (FMResultSet *)executeQueryWithSql:(NSString *)sql withArgumentsInArray:(NSArray*)argue{
//    FMResultSet* rs = [self.db executeQuery:sql withArgumentsInArray:argue];
//    if (!rs) {
//        DLogLine(@"ERROR: %d - %@",self.db.lastErrorCode, self.db.lastErrorMessage);
//    }
//    return rs;
//}

#pragma mark - 下面是案例

- (void)operateDB
{
    if (![_db open]) {
        DLogLine(@"不能打开数据库");
        return;
    }else
    {
        DLogLine(@"打开数据库成功");
    }
    
    [self updateARow];
    
    if ([_db close]) {
        DLogLine(@"数据库关闭成功");
    }else
    {
        DLogLine(@"数据库关闭失败");
    }
}

- (void)insertARow
{
    NSMutableDictionary *dictionaryArgs = [NSMutableDictionary dictionary];
    [dictionaryArgs setObject:@"Text1" forKey:@"membername"];
    [dictionaryArgs setObject:@"Text2" forKey:@"memberphone"];
    [dictionaryArgs setObject:[NSDate date] forKey:@"timestamp"];
    FMDBQuickCheck([_db executeUpdate:@"insert into Hb_member(membername,memberphone,timestamp) values (:membername, :memberphone, :timestamp)" withParameterDictionary:dictionaryArgs]);
}

- (void)selectRows
{
    FMResultSet *rs = [self.db executeQuery:@"select * from namedparamcounttest"];
    
    FMDBQuickCheck((rs != nil));
    
    while ([rs next]) {
        FMDBQuickCheck([[rs stringForColumn:@"a"] isEqualToString:@"Text1"]);
        FMDBQuickCheck([[rs stringForColumn:@"b"] isEqualToString:@"Text2"]);
        FMDBQuickCheck([rs intForColumn:@"c"] == 1);
        FMDBQuickCheck([rs doubleForColumn:@"d"] == 2.0);
    }
    [rs close];
}

- (void)updateARow
{
    NSMutableDictionary *dictionaryArgs = [NSMutableDictionary dictionary];
    
    [dictionaryArgs setObject:@"NewText1" forKey:@"a"];
    [dictionaryArgs setObject:@"NewText2" forKey:@"b"];
    [dictionaryArgs setObject:@"OneMoreText" forKey:@"OneMore"];
    
    BOOL rc = [self.db executeUpdate:@"update namedparamcounttest set a = :a, b = :b where b = 'Text2'" withParameterDictionary:dictionaryArgs];
    
//    FMDBQuickCheck(rc);
    
    if (!rc) {
        DLogLine(@"ERROR: %d - %@", self.db.lastErrorCode, self.db.lastErrorMessage);
    }
}

- (void)deleteARow
{
    NSMutableDictionary *dictionaryArgs = [NSMutableDictionary dictionary];
    [dictionaryArgs setObject:@"Text1" forKey:@"membername"];
    FMDBQuickCheck([_db executeUpdate:@"delete from table where a = :a" withParameterDictionary:dictionaryArgs]);
}

- (void)dealloc
{
    if ([_db close]) {
        DLogLine(@"数据库关闭成功");
    }else
    {
        DLogLine(@"数据库关闭失败");
    }
    
}

@end
